package com.xju.dao;

import com.xju.common.utils.JdbcUtils;
import com.xju.common.utils.PageInfo;
import com.xju.entity.Books;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class BookDao {
    public PageInfo<Books> pageList(int pageNo, int pageSize,String key) {
        //操作数据库
        try {
            //获取连接
            Connection connection = JdbcUtils.getConnection();
            String sqlWhere = "";
            boolean flag=key!=null && !key.trim().isEmpty();
            if(flag){
                sqlWhere = " where Title like ? or AuthorName like ? or ISBN like ? or Publisher like ? ";
            }
            //sql
            String sql = "select *  from books b " + sqlWhere+ " limit ?,?";
            //count
                String sqlCount = "select count(*) from books b "+sqlWhere;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            PreparedStatement preparedStatement1=connection.prepareStatement(sqlCount);
            //设置参数
            if(flag)
            {
                preparedStatement.setString(1,"%"+key.trim()+"%");
                preparedStatement.setString(2,"%"+key.trim()+"%");
                preparedStatement.setString(3,"%"+key.trim()+"%");
                preparedStatement.setString(4,"%"+key.trim()+"%");
                preparedStatement.setInt(5,(pageNo-1)*pageSize);
                preparedStatement.setInt(6,pageSize);

                //count
                preparedStatement1.setString(1,"%"+key.trim()+"%");
                preparedStatement1.setString(2,"%"+key.trim()+"%");
                preparedStatement1.setString(3,"%"+key.trim()+"%");
                preparedStatement1.setString(4,"%"+key.trim()+"%");
            }else {
                preparedStatement.setInt(1,(pageNo-1)*pageSize);
                preparedStatement.setInt(2,pageSize);
            }
            ResultSet rs = preparedStatement.executeQuery();
            List<Books> list = new ArrayList<Books>();
            while (rs.next()) {
                Books books = new Books();
                books.setTitle(rs.getString("Title"));
                books.setAuthorName(rs.getString("AuthorName"));
                books.setBookID(rs.getInt("BookID"));
                books.setTitle(rs.getString("Title"));
                books.setiSBN(rs.getString("ISBN"));
                books.setPublisher(rs.getString("Publisher"));
                books.setPublicationDate(rs.getDate("PublicationDate"));
                books.setFlag(rs.getInt("Flag"));

                list.add(books);
            }

            ResultSet resultSet = preparedStatement1.executeQuery();
            int count = 0;
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
            PageInfo <Books>pageInfo = new PageInfo(pageNo, pageSize, count, list);
            JdbcUtils.close(connection, preparedStatement, rs);
            JdbcUtils.close(connection,preparedStatement1,resultSet);
            return pageInfo;
        }
        catch (Exception e) {
            throw new RuntimeException(e);

        }
    }

    public void insert(String title, String authorName, String isbn, String publisher, String publicationDate, String flag) {
        try {
            //获取连接
            Connection connection = JdbcUtils.getConnection();
            //sqL
            String sql = "insert into books(Title,ISBN,Publisher,PublicationDate,AuthorName,flag) values(?,?,?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,title);
            preparedStatement.setString(2,isbn);
            preparedStatement.setString(3,publisher);
            preparedStatement.setString(4,publicationDate);
            preparedStatement.setString(5,authorName);
            preparedStatement.setInt(6, Integer.parseInt(flag));
            preparedStatement.executeUpdate();
            JdbcUtils.close(connection,preparedStatement,null);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public Books getOne(String bookID) {
        try {
            Connection connection = JdbcUtils.getConnection();
            String sql = "select *  from books b  where b.BookID=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,bookID);
            ResultSet resultSet = preparedStatement.executeQuery();
            Books books = new Books();
            while (resultSet.next()) {
                books.setBookID(resultSet.getInt("BookID"));
                books.setTitle(resultSet.getString("Title"));
                books.setAuthorName(resultSet.getString("AuthorName"));
                books.setiSBN(resultSet.getString("ISBN"));
                books.setPublisher(resultSet.getString("Publisher"));
                books.setPublicationDate(resultSet.getDate("PublicationDate"));
                books.setFlag(resultSet.getInt("Flag"));
            }
            return books;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public void updataByBookID(String bookID,String title, String authorName, String isbn, String publisher, String publicationDate, String flag) {
        try{
            Connection connection = JdbcUtils.getConnection();
            String sql="UPDATE books set Title=?,AuthorName=?,ISBN=?,Publisher=?,PublicationDate=?,flag=?  where BookID=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,title);
            preparedStatement.setString(2,authorName);
            preparedStatement.setString(3,isbn);
            preparedStatement.setString(4,publisher);
            preparedStatement.setString(5,publicationDate);
            preparedStatement.setInt(6, Integer.parseInt(flag));
            preparedStatement.setInt(7, Integer.parseInt(bookID));
            preparedStatement.execute();
            JdbcUtils.close(connection,preparedStatement,null);

        }catch (Exception e) {

        }
    }

    public void delete(String bookID) {
        try {
            Connection connection = JdbcUtils.getConnection();
            String sql = "delete from books b where b.BookID=?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,bookID);
            preparedStatement.execute();
            JdbcUtils.close(connection,preparedStatement,null);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


}























